package com.zjgj.modelimport;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.zjgj.POJO.ModelImportFormSet;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


//import sys.exception.BusinessException;
/**
 * Excel模板导入
 * Create Date:2021-08-20
 * Author:Tang
 */
public class ExcelImportUtil {
	
	private static int keyColumn=1;
	/**
	 * 获取sheet表中的数据
	 * 
	 * @param sheet  EXCEL页
	 * @param list   导入接口
	 * @param startROW   开始行
	 * @return　map
	 * 
	 */

	private static Map<String, Object> getData(Sheet sheet, List<ModelImportFormSet> list, int startROW) throws Exception {
		
		Map<String, Object> hashMap = new HashMap<String, Object>();
		String msg = "";		
		List<Map<String, String>> lsSet = getListMap(list);
		
		List<Hashtable<String, String>> ls = new ArrayList<Hashtable<String, String>>();
		Hashtable<String, String> lineMap = null;
		//数据从startRow行开始导入，行数从0开始计算
		for (int j = startROW; j <= sheet.getLastRowNum(); j++) {		 
			Row row = sheet.getRow(j);
			if(row==null) break;
			Cell cellKey=row.getCell(keyColumn-1);
			if(cellKey==null || getCellValue(cellKey).equals("")) break;   //关键列为空跳出循环   	    			
			lineMap = new Hashtable<String, String>(); // 用于接收每列的数据。
			if (lsSet != null && lsSet.size() > 0) {
				for (int i = 0; i < lsSet.size(); i++) {
					int culum=Integer.parseInt(lsSet.get(i).get("serialNumber"));
					String headername=lsSet.get(i).get("headername");
					Cell cell = row.getCell(culum - 1);					
					String k = getCellValue(cell); // 用于接收每个单元格的数据。	
					
					if (lsSet.get(i).get("checkMethod") != null && lsSet.get(i).get("checkMethod").trim().length()>0) {
						String checkMethod = lsSet.get(i).get("checkMethod");
						String[] checks = checkMethod.split(",");
						for (int n = 0; n < checks.length; n++) {
							if (("isNotNull").equals(checks[n])) {
								if (!isNotNull(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不能为空!";
									}
							}
							if (("isNumber").equals(checks[n])) {
								if (!isNumber(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不是数字!";								
									}
							}
							if (("isNonnegativeNumber").equals(checks[n])) {
								if (!isNonnegativeNumber(k)) {
									msg += "导入失败，第"+ (j + 1)+ "行，["+ headername+ "]不是非负数!";							
								}
							}
						}
					}
					
					lineMap.put(lsSet.get(i).get("columnName"), k); // 赋值。					
				}
			}
			if (!lineMap.isEmpty()) { // 判断是不是为空
				ls.add(lineMap);
			}
		}
		hashMap.put("msg", msg);
		hashMap.put("list", ls);
		// lineMap.put("tableName",tableName);
		// ls.add(lineMap);
		return hashMap;
	}
	
	
	/**
	 * 获取sheet表中的数据
	 * 
	 * @param sheet  EXCEL页
	 * @param list   导入接口
	 * @param startROW   开始行
	 * @return　map
	 * 
	 */	
	private static Map<String, Object> ocrGetData(Sheet sheet,List<ModelImportFormSet> list,int startROW) throws Exception {
		Map<String, Object> hashMap = new HashMap<String, Object>();
		String msg = "";		
		List<Map<String, String>> lsSet = getListMap(list);
		List<Hashtable<String, String>> ls = new ArrayList<Hashtable<String, String>>();
		Hashtable<String, String> lineMap = null;
		//数据从startRow行开始导入，行数从0开始计算
		int count=0;
		for (int j = startROW; j <= sheet.getLastRowNum(); j++) {		 
			Row row = sheet.getRow(j);
			if(row==null) continue;
			Cell cellKey=row.getCell(keyColumn-1);
			if(cellKey==null || getCellValue(cellKey).equals("")) {
				count++;
				if(count>4){
					break;   //关键列为空跳出循环   	    			
				}else{
					continue;
				}
			}
			lineMap = new Hashtable<String, String>(); // 用于接收每列的数据。
			if (lsSet != null && lsSet.size() > 0) {
				for (int i = 0; i < lsSet.size(); i++) {
					int culum=Integer.parseInt(lsSet.get(i).get("serialNumber"));
					String headername=lsSet.get(i).get("headername");
					Cell cell = row.getCell(culum - 1);					
					String k = getCellValue(cell); // 用于接收每个单元格的数据。	
					
					if (lsSet.get(i).get("checkMethod") != null && lsSet.get(i).get("checkMethod").trim().length()>0) {
						String checkMethod = lsSet.get(i).get("checkMethod");
						String[] checks = checkMethod.split(",");
						for (int n = 0; n < checks.length; n++) {
							if (("isNotNull").equals(checks[n])) {
								if (!isNotNull(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不能为空!";
									}
							}
							if (("isNumber").equals(checks[n])) {
								if (!isNumber(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不是数字!";								
									}
							}
							if (("isNonnegativeNumber").equals(checks[n])) {
								if (!isNonnegativeNumber(k)) {
									msg += "导入失败，第"+ (j + 1)+ "行，["+ headername+ "]不是非负数!";							
								}
							}
						}
					}
					
					lineMap.put(lsSet.get(i).get("columnName"), k); // 赋值。					
				}
			}
			if (!lineMap.isEmpty()) { // 判断是不是为空
				
				 Pattern pattern = Pattern.compile("[0-9]+");  

				 Matcher matcher = pattern.matcher((CharSequence) lineMap.get("item"));  

				  boolean result = matcher.matches();  
				  System.err.println(result);
				  if (result) {  

					  ls.add(lineMap);

				    }
			}
			count=0;
		}
		System.err.println(ls.size()+"``````````````````111111111111111111111111");
		hashMap.put("msg", msg);
		hashMap.put("list", ls);
		// lineMap.put("tableName",tableName);
		// ls.add(lineMap);
		return hashMap;
	}
	
	
	
	private static Map<String, Object> ocrOutGetData(Sheet sheet,List<ModelImportFormSet> list,int startROW) throws Exception {
		Map<String, Object> hashMap = new HashMap<String, Object>();
		String msg = "";		
		List<Map<String, String>> lsSet = getListMap(list);
		List<Hashtable<String, String>> ls = new ArrayList<Hashtable<String, String>>();
		Hashtable<String, String> lineMap = null;
		//数据从startRow行开始导入，行数从0开始计算
		int count=0;
		for (int j = startROW; j <= sheet.getLastRowNum(); j++) {		 
			Row row = sheet.getRow(j);
			if(row==null) continue;
			Cell cellKey=row.getCell(keyColumn-1);
			if(cellKey==null || getCellValue(cellKey).equals("")) {
				count++;
				if(count>4){
					break;   //关键列为空跳出循环   	    			
				}else{
					continue;
				}
			}
			lineMap = new Hashtable<String, String>(); // 用于接收每列的数据。
			if (lsSet != null && lsSet.size() > 0) {
				for (int i = 0; i < lsSet.size(); i++) {
					int culum=Integer.parseInt(lsSet.get(i).get("serialNumber"));
					String headername=lsSet.get(i).get("headername");
					Cell cell = row.getCell(culum - 1);					
					String k = getCellValue(cell); // 用于接收每个单元格的数据。	
					
					if (lsSet.get(i).get("checkMethod") != null && lsSet.get(i).get("checkMethod").trim().length()>0) {
						String checkMethod = lsSet.get(i).get("checkMethod");
						String[] checks = checkMethod.split(",");
						for (int n = 0; n < checks.length; n++) {
							if (("isNotNull").equals(checks[n])) {
								if (!isNotNull(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不能为空!";
									}
							}
							if (("isNumber").equals(checks[n])) {
								if (!isNumber(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不是数字!";								
									}
							}
							if (("isNonnegativeNumber").equals(checks[n])) {
								if (!isNonnegativeNumber(k)) {
									msg += "导入失败，第"+ (j + 1)+ "行，["+ headername+ "]不是非负数!";							
								}
							}
						}
					}
					
					lineMap.put(lsSet.get(i).get("columnName"), k); // 赋值。					
				}
			}
			if (!lineMap.isEmpty()) { // 判断是不是为空
  
						  ls.add(lineMap);

				    
			}
			count=0;
		}
		hashMap.put("msg", msg);
		hashMap.put("list", ls);
		// lineMap.put("tableName",tableName);
		// ls.add(lineMap);
		return hashMap;
	}
	
	
	private static Map<String, Object> ocrGetData2(Sheet sheet,List<ModelImportFormSet> list,int startROW,String bh) throws Exception {
		Map<String, Object> hashMap = new HashMap<String, Object>();
		String msg = "";		
		List<Map<String, String>> lsSet = getListMap(list);
		List<Hashtable<String, String>> ls = new ArrayList<Hashtable<String, String>>();
		Hashtable<String, String> lineMap = null;
		//数据从startRow行开始导入，行数从0开始计算
		int count=0;
		for (int j = startROW; j <= sheet.getLastRowNum(); j++) {		 
			Row row = sheet.getRow(j);
			if(row==null) continue;
			Cell cellKey=row.getCell(keyColumn-1);
			if(cellKey==null || getCellValue(cellKey).equals("")) {
				count++;
				if(count>4){
					break;   //关键列为空跳出循环   	    			
				}else{
					continue;
				}
			}
			lineMap = new Hashtable<String, String>(); // 用于接收每列的数据。
			if (lsSet != null && lsSet.size() > 0) {
				for (int i = 0; i < lsSet.size(); i++) {
					int culum=Integer.parseInt(lsSet.get(i).get("serialNumber"));
					String headername=lsSet.get(i).get("headername");
					Cell cell = row.getCell(culum - 1);					
					String k = getCellValue(cell); // 用于接收每个单元格的数据。	
					
					if (lsSet.get(i).get("checkMethod") != null && lsSet.get(i).get("checkMethod").trim().length()>0) {
						String checkMethod = lsSet.get(i).get("checkMethod");
						String[] checks = checkMethod.split(",");
						for (int n = 0; n < checks.length; n++) {
							if (("isNotNull").equals(checks[n])) {
								if (!isNotNull(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不能为空!";
									}
							}
							if (("isNumber").equals(checks[n])) {
								if (!isNumber(k)) {
									msg += "第"+ (j + 1)+ "行，["+ headername+ "]不是数字!";								
									}
							}
							if (("isNonnegativeNumber").equals(checks[n])) {
								if (!isNonnegativeNumber(k)) {
									msg += "导入失败，第"+ (j + 1)+ "行，["+ headername+ "]不是非负数!";							
								}
							}
						}
					}
					
					lineMap.put(lsSet.get(i).get("columnName"), k); // 赋值。					
				}
			}
			if (!lineMap.isEmpty()) { // 判断是不是为空
				


					  ls.add(lineMap);

				    
			}
			count=0;
		}
		hashMap.put("msg", msg);
		hashMap.put("list", ls);
		// lineMap.put("tableName",tableName);
		// ls.add(lineMap);
		return hashMap;
	}
	
	
	private static Map<String, Object> getData(Sheet sheet,List<ModelImportFormSet> list) throws Exception {
		return getData(sheet,list,1);    //默认跳过第一行
	}
	
	/**
	 * 得到关键列，关键列的值为空则不再获取此行（包括此行）以下的数据
	 * @param list
	 * @return
	 */
	private static List<Map<String, String>>getListMap(List<ModelImportFormSet> list){
		List<Map<String, String>> lsSet = new ArrayList<Map<String, String>>();
		Map<String, String> map = null;
   
		if (list != null && list.size() > 0) {
			for (int m = 0; m < list.size(); m++) {
				map = new HashMap<String, String>();
				ModelImportFormSet formSet = list.get(m);
				if (formSet != null && formSet.getColumnname() != null && !formSet.getColumnname().trim().equals("")) {
					// String tableName=formSet.getTablename();
					String columnName = formSet.getColumnname();//得到英文列名
					String serialNumber = formSet.getSerialnumber();//得到列的顺序
					String headername=formSet.getHeadername();//得到列明
					String isKey=formSet.getIs_key();
					if(isKey!=null && isKey.equals("Y")) 
						keyColumn=Integer.parseInt(serialNumber);//得到关键列是第几列
					
					map.put("columnName", columnName);
					map.put("serialNumber", serialNumber);
					map.put("headername", headername);

					lsSet.add(map);
				}
			}
		}
		return lsSet;
	}
	
	/**
	 * 获取某个单元格的值
	 * @param rowNo  第几行
	 * @param columnNo  第几列
	 * @return
	 */
	public static String getCellValue(File file, int sheetNum,int rowNo,int columnNo){
		Workbook book = null;		
		try {
			book = WorkbookFactory.create(file);		
			Sheet sheet=book.getSheetAt(sheetNum - 1);
			Row row = sheet.getRow(rowNo-1);		
			Cell cell = row.getCell(columnNo - 1);					
			return getCellValue(cell); // 用于接收每个单元格的数据。	
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
			return "";
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		
	}
	
	/**
	 * 获取单元格的值
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell){
		String k="";
		if(cell!=null){
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			k = "";
			break;
		case Cell.CELL_TYPE_ERROR:
			k = Byte.toString(cell.getErrorCellValue());
			break;
		case Cell.CELL_TYPE_STRING:
			k = cell.getRichStringCellValue().getString().trim().replace(",", "");
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd");
				k = sdf.format(cell.getDateCellValue());
			} else {
				double b = cell.getNumericCellValue();
				int a = (int) b;
				k = a == b ? Integer.toString(a) : Double.toString(b);
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			k = Boolean.toString(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			k = cell.getCellFormula();
			break;
		default:
			k = "";
		}
		}
		return k;
	}

	/**
	 * 读取excel文件，可以是2003,2007
	 * 
	 * @param filePath
	 *            文件路径
	 * @param sheetNum
	 * @return
	 */
	public static Map<String, Object> readExcelFile(String filePath,
			int sheetNum, List<ModelImportFormSet> ls) throws Exception {

		InputStream ins = null;
		Workbook book = null;
		Map<String, Object> list = null;
		try {
			ins = new FileInputStream(filePath);
			book = WorkbookFactory.create(ins);
			list = getData(book.getSheetAt(sheetNum - 1), ls);
			ins.close();

		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (ins != null) {
				try {
					ins.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public static Map<String, Object> readExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls) throws Exception {
		return readExcelFile(file,sheetNum,ls,1);
	}
	
	
	
	public static Map<String, Object> ocrExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls) throws Exception {
		return ocrExcelFile(file,sheetNum,ls,1);
	}
	
	public static Map<String, Object> ocrOutExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls) throws Exception {
		return ocrOutExcelFile(file,sheetNum,ls,1);
	}
	
	public static Map<String, Object> ocrExcelFile2(File file, int sheetNum,
			List<ModelImportFormSet> ls,String bh) throws Exception {
		return ocrExcelFile2(file,sheetNum,ls,1,bh);
	}
	
	public static Map<String, Object> ocrExcelFile2(File file, int sheetNum,
			List<ModelImportFormSet> ls,int startRow,String bh) throws Exception {
		Workbook book = null;
		Map<String, Object> list = null;
		try {
			FileInputStream fileInputStream=new FileInputStream(file);
			book = WorkbookFactory.create(new BufferedInputStream(fileInputStream));		
			list = ocrGetData2(book.getSheetAt(sheetNum), ls,startRow,bh);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static Map<String, Object> ocrOutExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls,int startRow) throws Exception {
		Workbook book = null;
		Map<String, Object> list = null;
		try {
			FileInputStream fileInputStream=new FileInputStream(file);
			book = WorkbookFactory.create(new BufferedInputStream(fileInputStream));		
			list = ocrOutGetData(book.getSheetAt(sheetNum - 1), ls,startRow);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static Map<String, Object> ocrExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls,int startRow) throws Exception {
		Workbook book = null;
		Map<String, Object> list = null;
		try {
			FileInputStream fileInputStream=new FileInputStream(file);
			book = WorkbookFactory.create(new BufferedInputStream(fileInputStream));		
			list = ocrGetData(book.getSheetAt(sheetNum - 1), ls,startRow);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public static Map<String, Object> readExcelFile(File file, int sheetNum,
			List<ModelImportFormSet> ls,int startRow) throws Exception {
		Workbook book = null;
		Map<String, Object> list = null;
		try {
			FileInputStream fileInputStream=new FileInputStream(file);
			book = WorkbookFactory.create(new BufferedInputStream(fileInputStream));		
			list = getData(book.getSheetAt(sheetNum - 1), ls,startRow);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 是否不为null
	public static boolean isNotNull(String str) {
		boolean b = false;
		if (str != null && !str.trim().equals("")) {
			b = true;
		}
		return b;
	}

	// 是否为数字
	public static boolean isNumber(String str) {
		boolean b = false;
		if (str==null||str.trim().length()==0||str.matches("^[0-9]*$")) {
			b = true;
		}
		return b;
	}

	// 是否为非负数
	public static boolean isNonnegativeNumber(String str) {
		boolean b = false;
		if(str!=null) str=str.replace(",", ""); //去掉数字中的逗号
		if (str==null||str.trim().length()==0||str.matches("^(\\d*\\.)?\\d+$")) {
			b = true;
		}
		return b;
	}
}
